﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Pku.BookStore.Model;
using System.Data;
using System.Data.Common;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class CategoryDAL : BaseDAL
    {
        string commandText = string.Empty;
        /// <summary>
        /// 增加分类
        /// </summary>
        /// <param name="entity"></param>
        public int Add(CategoryModel entity)
        {
            commandText = @"INSERT INTO Category(CategoryName)
VALUES(@CategoryName);
SET  @CategoryID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CategoryName", DbType.String, entity.CategoryName);
            DbMain.AddOutParameter(command, "CategoryID", DbType.Int32, 10);
            DbMain.ExecuteNonQuery(command);

            int CategoryID = (int)DbMain.GetParameterValue(command, "CategoryID");
            return CategoryID;
        }

        /// <summary>
        /// 修改分类
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(CategoryModel entity)
        {
            commandText = @"UPDATE Category SET CategoryName=@CategoryName WHERE CategoryID=@CategoryID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CategoryID", DbType.Int32, entity.CategoryID);
            DbMain.AddInParameter(command, "CategoryName", DbType.String, entity.CategoryName);

            return (int)DbMain.ExecuteNonQuery(command) > 0;
        }

        /// <summary>
        /// 搜索
        /// </summary>
        /// <returns></returns>
        public List<CategoryModel> Search(Model.CategoryParam param)
        {
            var list = new List<Model.CategoryModel>();
            DbCommand dbCommand = DbMain.GetStoredProcCommand("CategoryPageSearch");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "Page", DbType.Int32, param.PageNo);
            DbMain.AddInParameter(dbCommand, "Rows", DbType.Int32, param.PageRow);
            DbMain.AddInParameter(dbCommand, "Level", DbType.Int32, param.Level);
            DbMain.AddInParameter(dbCommand, "pCatid", DbType.Int32, param.pCatid);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns></returns>
        public CategoryModel GetEntityById(int categoryID)
        {
            commandText = @"SELECT *  FROM  Category WHERE CategoryID=@CategoryID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CategoryID", DbType.Int32, categoryID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    return ConvertReaderToEntity(dr);
                }
            }
            return null;
        }

        /// <summary>
        /// 转实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static CategoryModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new CategoryModel
            {
                CategoryID = (int)dr["CategoryID"],
                CategoryName = dr["CategoryName"].ToString(),
                Level = (int)dr["Level"],
                pCatid = (int)dr["pCatid"]
            };
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(string Str)
        {
            commandText = "SELECT COUNT(1) FROM Category c WHERE c.CategoryName=@CategoryName";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "CategoryName", DbType.String, Str);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Model.CategoryParam param)
        {
            string commandText = "SELECT COUNT(1) FROM Category o(NOLOCK) WHERE 1=1";
            var command = DbMain.GetSqlStringCommand(commandText);
            return (int)DbMain.ExecuteScalar(command);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public bool Delete(int CategoryID)
        {
            commandText = "DELETE Category WHERE CategoryID=@CategoryID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "CategoryID", DbType.Int32, CategoryID);
            return (int)DbMain.ExecuteNonQuery(command) > 0;
        }

        /// <summary>
        /// 取同级分类
        /// </summary>
        /// <param name="Level"></param>
        /// <param name="pCatid"></param>
        /// <returns></returns>
        public List<CategoryModel> GetLevelSame(int Level, int pCatid)
        {
            var list = new List<Model.CategoryModel>();
            commandText = "SELECT * FROM Category c(NOLOCK) WHERE 1=1 AND c.[Level]=@Level";
            if (Level == 2)
            {
                commandText += " AND c.pCatid=@pCatid";
            }
            var dbCommand = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(dbCommand, "Level", DbType.Int32, Level);
            DbMain.AddInParameter(dbCommand, "pCatid", DbType.Int32, pCatid);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    list.Add(ConvertReaderToEntity(dr));
                }
            }
            return list;
        }
    }

}